------------------------------------------------
-- @name: star_transformation_bitmap_join_index
-- @author: dion cho
-- @descripiton: test for star transformation
------------------------------------------------

-- create objects
-- this is star schema
drop table t_fact purge;

drop table t_dim1 purge;

drop table t_dim2 purge;


create table t_fact(dim1_key int, dim2_key int, dim3_key int, value1 int, value2 int);

alter table t_fact add constraint pk_fact primary key(dim1_key, dim2_key, dim3_key);

create table t_dim1(dim1_key int primary key, value varchar2(20));

create table t_dim2(dim2_key int primary key, value varchar2(20));

alter table t_fact add constraint fk_dim1 
    foreign key (dim1_key)
    references t_dim1(dim1_key);

alter table t_fact add constraint fk_dim2
    foreign key (dim2_key)
    references t_dim2(dim2_key);


-- generate data
-- generate dimension data
insert into t_dim1
select
    level as dim1_key,
    'area'||level as value
from
    dual
connect by level <= 100;

insert into t_dim2
select
    level as dim2_key,
    'code'||level as value
from
    dual
connect by level <= 100;


-- generate fact data
insert /*+ append */ into t_fact
select
    t_dim1.dim1_key,
    t_dim2.dim2_key,
    dummy.dim3_key,
    dbms_random.random as value1,
    dbms_random.random as value2    
from
    t_dim1, t_dim2, 
    (select level as dim3_key from dual connect by level <= 10) dummy
;

commit;



-- create bitmap index
create bitmap index t_fact_bidx1 on t_fact(dim1_key);
create bitmap index t_fact_bidx2 on t_fact(dim2_key);


-- gather statistics
exec dbms_stats.gather_table_stats(user, 't_dim1', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_dim2', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_fact', cascade=>true, no_invalidate=>false);

-- bump up table stats
--exec dbms_stats.set_table_stats(user, 't_dim1', numrows=>100000, no_invalidate=>false);
--exec dbms_stats.set_table_stats(user, 't_dim2', numrows=>100000, no_invalidate=>false);
--exec dbms_stats.set_table_stats(user, 't_fact', numrows=>100000000, no_invalidate=>false);



-- case 1: no star transformation  
-- disable start transformation
alter session set star_transformation_enabled = false;

select /*+ gather_plan_statistics */
    d1.value as area,
    d2.value as code,
    sum(f.value1) as sum_value1,
    max(f.value2) as max_value2,
    count(*) as counts
from
    t_fact f,
    t_dim1 d1,
    t_dim2 d2
where
    f.dim1_key = d1.dim1_key and
    f.dim2_key = d2.dim2_key and
    d1.value like 'area1%' and
    d2.value = 'code1'
group by
    d1.value, d2.value
;

@stat


select /*+ gather_plan_statistics 
      index_combine(f t_fact_bidx1 t_fact_bidx2) */
    d1.value as area,
    d2.value as code,
    sum(f.value1) as sum_value1,
    max(f.value2) as max_value2,
    count(*) as counts
from
    t_fact f,
    t_dim1 d1,
    t_dim2 d2
where
    f.dim1_key = d1.dim1_key and
    f.dim2_key = d2.dim2_key and
    d1.value like 'area1%' and
    d2.value = 'code1'
group by
    d1.value, d2.value
;

@stat


-- case 2: star transformation
alter session set star_transformation_enabled = true;

select /*+ gather_plan_statistics */
    d1.value as area,
    d2.value as code,
    sum(f.value1) as sum_value1,
    max(f.value2) as max_value2,
    count(*) as counts
from
    t_fact f,
    t_dim1 d1,
    t_dim2 d2
where
    f.dim1_key = d1.dim1_key and
    f.dim2_key = d2.dim2_key and
    d1.value like 'area1%' and
    d2.value = 'code1'
group by
    d1.value, d2.value
;

@stat


-- case 3: bitmap join index
drop index t_fact_bidx1;
drop index t_fact_bidx2;

create bitmap index t_fact_bjidx1 on t_fact(d1.value)
from t_fact f, t_dim1 d1
where 
    f.dim1_key = d1.dim1_key
;

create bitmap index t_fact_bjidx2 on t_fact(d2.value)
from t_fact f, t_dim2 d2
where
    f.dim2_key = d2.dim2_key
;

-- gather statistics
exec dbms_stats.gather_table_stats(user, 't_dim1', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_dim2', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_fact', cascade=>true, no_invalidate=>false);


select /*+ gather_plan_statistics */
    d1.value as area,
    d2.value as code,
    sum(f.value1) as sum_value1,
    max(f.value2) as max_value2,
    count(*) as counts
from
    t_fact f,
    t_dim1 d1,
    t_dim2 d2
where
    f.dim1_key = d1.dim1_key and
    f.dim2_key = d2.dim2_key and
    d1.value like 'area1%' and
    d2.value = 'code1'
group by
    d1.value, d2.value
;

@stat




select /*+ index_combine(f t_fact_bjidx1 t_fact_bjidx2) 
        gather_plan_statistics */
    d1.value as area,
    d2.value as code,
    sum(f.value1) as sum_value1,
    max(f.value2) as max_value2,
    count(*) as counts
from
    t_fact f,
    t_dim1 d1,
    t_dim2 d2
where
    f.dim1_key = d1.dim1_key and
    f.dim2_key = d2.dim2_key and
    d1.value like 'area1%' and
    d2.value = 'code1'
group by
    d1.value, d2.value
;

@stat
